package com.app.megaprosoft.DB;

import java.io.File;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;

import com.app.megaprosoft.general.DateTime;
import com.app.megaprosoft.modyimcarcare.Carcare_Login;

import android.app.Application;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.location.Address;
import android.os.Environment;
import android.util.Log;

public class SQLiteDBClass extends SQLiteOpenHelper {

	ResultSet rs = null;
	// Database Version
	private static final int 	DATABASE_VERSION = 1;

	// Database Name
	private static final String DATABASE_NAME 						= "MegaAutoCare";
	// User
	private static final String TABLE_FFUSERGROUP 					= "_FFUSERGROUP";
	private static final String TABLE_FFUSER 						= "_FFUSER";
	// ArFF
//	private static final String TABLE_ArFF = "_ArFF";
	private static final String TABLE_ArGroupMain 					= "_ArGroupMain";
	private static final String TABLE_ArGroup 						= "_ArGroup";
	private static final String TABLE_Addresses 					= "_Addresses";
	// ArFFCar
	private static final String TABLE_CarGroup 						= "_CarGroup";
	private static final String TABLE_CarBands 						= "_CarBands";
	private static final String TABLE_CarGenerations 				= "_CarGenerations";
	private static final String TABLE_Colors 						= "_Colors";
	// Item
	private static final String TABLE_Item 							= "_Item";
	private static final String TABLE_ItemGroup 					= "_ItemGroup";
	private static final String TABLE_ItemGroupMain					= "_ItemGroupMain";
	private static final String TABLE_ItemPrice 					= "_ItemPrice";
	private static final String TABLE_ItemPromotionPrices 			= "_ItemPromotionPrices";
	private static final String TABLE_PromotionGiveaways 			= "_PromotionGiveaways";
	private static final String TABLE_PromotionGiveawayDetails 		= "_PromotionGiveawayDetails";
	private static final String TABLE_ItemSize 						= "_ItemSize";
	private static final String TABLE_ItemStock 					= "_ItemStock";
	private static final String TABLE_ItemUnit 						= "_ItemUnit";
	// Service
//	private static final String TABLE_ServicePOS = "_ServicePOS";
//	private static final String TABLE_ServicePOSLists = "_ServicePOSLists";

	public SQLiteDBClass(Context context) {
		super(context, Environment.getExternalStorageDirectory()
				+ File.separator + "/DataBase/" + File.separator
				+ DATABASE_NAME, null, DATABASE_VERSION);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {

		// TODO Auto-generated method stub
		try {
			db.execSQL("CREATE TABLE " + TABLE_FFUSERGROUP
					+ "(FFUSERGROUPOID TEXT(100) PRIMARY KEY,"
					+ " UserGroupCode TEXT(100),"
					+ " UserGroupName TEXT(100));");
		} catch (Exception e) {

		}
		
		try {
			db.execSQL("CREATE TABLE " + TABLE_FFUSER
					+ "(FFUserOID TEXT(100) PRIMARY KEY,"
					+ " LoginName TEXT(100)," + " UserName TEXT(100),"
					+ " Password TEXT(100)," + " DepCode TEXT(100),"
					+ " DepName TEXT(100)," + " UserGroupCode TEXT(100),"
					+ " UserGroupName TEXT(100)," + " PicPath TEXT(100));");
		} catch (Exception e) {

		}

		try {
			db.execSQL("CREATE TABLE " + TABLE_ArGroupMain
					+ "(ArGroupMainOID TEXT(100) PRIMARY KEY,"
					+ " ArGroupMainCode TEXT(100),"
					+ " ArGroupMainDesc TEXT(100),"
					+ " ArGroupMainDiscount TEXT(100));");
		} catch (Exception e) {

		}
		
		try {
			db.execSQL("CREATE TABLE " + TABLE_ArGroup
					+ "(ArGroupOID TEXT(100) PRIMARY KEY,"
					+ " ArGroupCode TEXT(100)," 
					+ " ArGroupDesc TEXT(100),"
					+ " ArGroupMainCode TEXT(100),"
					+ " ArGroupMainDesc TEXT(100),"
					+ " ArGroupMainDiscount TEXT(100));");
		} catch (Exception e) {

		}
		
		
		try {
			db.execSQL("CREATE TABLE " + TABLE_Addresses
					+ "(AddressesOID TEXT(100) PRIMARY KEY,"
					+ " AddressCode TEXT(100)," + " ProvinceName TEXT(100),"
					+ " Type TEXT(100)," + " ProvinceCode TEXT(100),"
					+ " IsFavorite TEXT(100));");
		} catch (Exception e) {

		}
		try {
			db.execSQL("CREATE TABLE " + TABLE_CarGroup
					+ "(CarGroupOID TEXT(100) PRIMARY KEY,"
					+ " CarGroupName TEXT(100)," 
					+ " CarSize TEXT(100));");
		} catch (Exception e) {

		}
		try {
			db.execSQL("CREATE TABLE " + TABLE_CarBands
					+ "(CarBandsOID TEXT(100) PRIMARY KEY,"
					+ " CarBandCode TEXT(100)," + " CarBandName TEXT(100),"
					+ " CarBandDescription TEXT(100)," + " OrderNo TEXT(100));");
		} catch (Exception e) {

		}
		try {
			db.execSQL("CREATE TABLE " + TABLE_CarGenerations
					+ "(CarGenerationsOID TEXT(100) PRIMARY KEY,"
					+ " CarGenerationCode TEXT(100),"
					+ " CarGenerationName TEXT(100),"
					+ " CarBandOID TEXT(100)," + " CarSize TEXT(100),"
					+ " CarGroupOID TEXT(100));");
		} catch (Exception e) {

		}
		try {
			db.execSQL("CREATE TABLE " + TABLE_Colors
					+ "(ColorsOID TEXT(100) PRIMARY KEY,"
					+ " ColorCode TEXT(100)," + " ColorName TEXT(100));");
		} catch (Exception e) {

		}

		try {
			db.execSQL("CREATE TABLE " + TABLE_Item
					+ "(ItemOID TEXT(100) PRIMARY KEY,"

					+ " ItemCode TEXT(100)," 
					+ " ItemName TEXT(100),"
					+ " ItemEngName TEXT(100)," 
					+ " ItemDesc TEXT(100),"
					+ " ItemPicPath TEXT(100),"

					+ " ItemUnitCode TEXT(100)," 
					+ " ItemUnitDesc TEXT(100),"
					+ " AltItemUnitCode TEXT(100),"
					+ " AltItemUnitDesc TEXT(100),"
					+ " ItemGroupOID TEXT(100),"

					+ " ItemGroupCode TEXT(100)," 
					+ " ItemGroupDesc TEXT(100),"
					+ " ItemGroupMainOID TEXT(100),"
					+ " ItemGroupMainCode TEXT(100),"
					+ " ItemGroupMainDesc TEXT(100),"

					+ " IsMainRawPart TEXT(100),"
					+ " ItemWeightingStatus TEXT(100),"
					+ " StdPrice TEXT(100)," 
					+ " AvgWeightAltUnit TEXT(100),"
					+ " ItemRawAverageWeight TEXT(100),"

					+ " BrandOID TEXT(100)," 
					+ " BrandCode TEXT(100),"
					+ " BrandName TEXT(100)," 
					+ " ItemSizeCode TEXT(100),"
					+ " ItemSizeName TEXT(100),"

					+ " ModelOID TEXT(100)," 
					+ " ModelCode TEXT(100),"
					+ " ModelName TEXT(100)," 
					+ " MinStock TEXT(100),"
					+ " StockOut TEXT(100),"

					+ " Cost TEXT(100)," 
					+ " IsReplaceItem TEXT(100),"
					+ " ReplaceItemPacketCode TEXT(100),"
					+ " ReItemName TEXT(100)," 
					+ " BPItemCode1 TEXT(100),"

					+ " BPItemCode2 TEXT(100)," 
					+ " BomDocCode TEXT(100),"
					+ " ApName TEXT(100)," 
					+ " IsStock TEXT(100),"
					+ " IsGeneric TEXT(100),"

					+ " IsBOM TEXT(100)," 
					+ " IsPurchase TEXT(100),"
					+ " IsSold TEXT(100)," 
					+ " IsDiscontinued TEXT(100),"
					+ " BarCodeUPC TEXT(100),"

					+ " BarCode2D TEXT(100)," 
					+ " Duration TEXT(100),"
					+ " LastBuyPrice TEXT(100)," 
					+ " IsPromotion TEXT(100),"
					+ " PromotionStartDate TEXT(100),"

					+ " PromotionEndDate TEXT(100));");

		} catch (Exception e) {

		}
		try {
			db.execSQL("CREATE TABLE " + TABLE_ItemGroup
					+ "(ItemGroupOID TEXT(100) PRIMARY KEY,"

					+ " ItemGroupCode TEXT(100)," + " ItemGroupDesc TEXT(100),"
					+ " ItemGroupMainCode TEXT(100),"

					+ " ItemGroupMainDesc TEXT(100));");
		} catch (Exception e) {

		}
		try {
			db.execSQL("CREATE TABLE " + TABLE_ItemGroupMain
					+ "(ItemGroupMainOID TEXT(100) PRIMARY KEY,"

					+ " ItemGroupMainCode TEXT(100),"
					+ " ItemGroupMainDesc TEXT(100));");
		} catch (Exception e) {

		}
		try {
			db.execSQL("CREATE TABLE " + TABLE_ItemPrice
					+ "(ItemPriceOID TEXT(100) PRIMARY KEY,"

					+ " CarTypeOID TEXT(100)," 
					+ " CarSizeOID TEXT(100),"
					+ " CarGroupOID TEXT(100)," 
					+ " ItemOID TEXT(100),"
					+ " ItemPrice TEXT(100),"

					+ " CarGroupName TEXT(100)," 
					+ " CarSizeName TEXT(100),"
					+ " CarTypeName TEXT(100)," 
					+ " ItemCode TEXT(100),"
					+ " ItemName TEXT(100),"

					+ " ItemGroupCode TEXT(100)," 
					+ " ItemGroupDesc TEXT(100),"
					+ " ItemGroupOID TEXT(100),"
					+ " ItemGroupMainCode TEXT(100),"
					+ " ItemGroupMainDesc TEXT(100),"

					+ " ItemGroupMainOID TEXT(100),"
					+ " ItemUnitDesc TEXT(100));");
		} catch (Exception e) {

		}
		try {
			db.execSQL("CREATE TABLE " + TABLE_PromotionGiveaways
					+ "(PromotionGOID TEXT(100) PRIMARY KEY,"

					+ " PromotionGName TEXT(100)," 
					+ " PromotionGDescription TEXT(100),"
					+ " PromotionGStartDate TEXT(100)," 
					+ " PromotionGEndDate TEXT(100));");
		} catch (Exception e) {

		}
		try {
			db.execSQL("CREATE TABLE " + TABLE_PromotionGiveawayDetails
					+ "(PromotionGDOID TEXT(100) PRIMARY KEY,"

					+ " PromotionGOID TEXT(100)," 
					+ " ItemOID TEXT(100)," 
					+ " PromotionGDType TEXT(100));");
		} catch (Exception e) {

		}
		try {
			db.execSQL("CREATE TABLE " + TABLE_ItemPromotionPrices
					+ "(ItemPromotionPricesOID TEXT(100) PRIMARY KEY,"

					+ " ItemCode TEXT(100)," + " ItemName TEXT(100),"
					+ " ItemGroupCode TEXT(100)," + " ItemGroupDesc TEXT(100),"
					+ " ItemGroupOID TEXT(100),"

					+ " ItemGroupMainCode TEXT(100),"
					+ " ItemGroupMainDesc TEXT(100),"
					+ " ItemGroupMainOID TEXT(100),"
					+ " ItemUnitDesc TEXT(100)," + " ItemPrice TEXT(100),"

					+ " PromotionType TEXT(100)," + " PromotionMode TEXT(100),"
					+ " PromotionDiscountPrice TEXT(100),"
					+ " PromotionDiscountPercent TEXT(100),"
					+ " ItemPromotionPrice TEXT(100));");
		} catch (Exception e) {

		}
		try {
			db.execSQL("CREATE TABLE " + TABLE_ItemSize
					+ "(ItemSizeOID TEXT(100) PRIMARY KEY,"

					+ " ItemSizeCode TEXT(100)," + " ItemSizeName TEXT(100),"
					+ " ContainSize TEXT(100)," + " ItemGroupCode TEXT(100),"
					+ " ItemGroupDesc TEXT(100));");
		} catch (Exception e) {

		}
		try {
			db.execSQL("CREATE TABLE " + TABLE_ItemStock
					+ "(ItemStockOID TEXT(100) PRIMARY KEY,"

					+ " ItemStockDesc TEXT(100));");
		} catch (Exception e) {

		}
		try {
			db.execSQL("CREATE TABLE " + TABLE_ItemUnit
					+ "(ItemUnitOID TEXT(100) PRIMARY KEY,"

					+ " ItemUnitCode TEXT(100)," + " ItemUnitDesc TEXT(100));");

		} catch (Exception e) {

		}

		Log.d("CREATE TABLE", "Create Table Successfully.");
	}

	public void Clear_table() {
		SQLiteDatabase db;
		db = this.getWritableDatabase();
		onUpgrade(db, 0, 1);
	}

	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_FFUSERGROUP);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_FFUSER);

//		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ArFF);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ArGroupMain);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ArGroup);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_Addresses);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_CarGroup);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_CarBands);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_CarGenerations);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_Colors);

		db.execSQL("DROP TABLE IF EXISTS " + TABLE_Item);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ItemGroup);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ItemGroupMain);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ItemPrice);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ItemPromotionPrices);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ItemSize);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ItemStock);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ItemUnit);
		
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_PromotionGiveaways);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_PromotionGiveawayDetails);
//
//		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ServicePOS);
//		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ServicePOSDetail);
//		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ServicePOSLists);

		// Re Create on method onCreate
		onCreate(db);
	}

}
